/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package escom.ipn.modelo.dao;


import escom.ipn.modelo.dto.PoliciaClass;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author db2admin
 */
public class adminPoliciaCRPDAO {
    
    public static final String SQL_INSERT =
            "insert into policia_radio_patrulla(CARRO_RADIO_PATRULLA_id_unidad,"
                              + "nombre,"
                              + "ap_paterno,"
                              + "ap_materno,"
                              + "direccion, "
                              + "telefono,"
                              + "grado,"
                              + "jornada) "
                              //+ "sector_id_sector) "  // cuando se agregue el sector
            + "values(?,?,?,?,?,?,?,?) ";
    
     public static final String SQL_UPDATE =
             "update policia_radio_patrulla set CARRO_RADIO_PATRULLA_id_unidad=?,"
                              + "nombre=?,"
                              + "ap_paterno=?,"
                              + "ap_materno=?,"
                              + "direccion=?, "
                              + "telefono=?,"
                              + "grado=?,"
                              + "jornada=?"
                              + " where id_recurso=?";

     
    public static final String SQL_SELECT_ALL =
             "select *from policia_radio_patrulla";
    
    private static final String SQL_DELETE =
            "delete from policia_radio_patrulla where id_recurso = ?";
    
    public static final String SQL_SELECT =
            "select * from policia_radio_patrulla where id_recurso= ?";
    
    public static final String SQL_SELECT_SECTOR =
            "select * from policia_radio_patrulla where nombre LIKE %? and nombre LIKE ?% and nombre LIKE %?% and SECTOR_id_sector=?";
   
    
     public void insertPolicia(PoliciaClass a, Connection conn)
            throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(SQL_INSERT);
            ps.setInt(1, a.getCrp());
            ps.setString(2, a.getNombre());
            ps.setString(3, a.getAp_paterno());
            ps.setString(4, a.getAp_materno());
            ps.setString(5, a.getDireccion());
            ps.setString(6, a.getTelefono());
            ps.setString(7, a.getGrado());
            ps.setInt(8, a.getJornada());
            //ps.setInt(8, a.getSector());
            ps.executeUpdate();
        } 
        finally {
            cerrar(ps);
            cerrar(conn);
        } 
     }
    
    public void updatePolicia(PoliciaClass a, Connection cnn) 
            throws SQLException {
         PreparedStatement ps = null;
        try {
            ps = cnn.prepareStatement(SQL_UPDATE);
            ps.setInt(1, a.getCrp());
            ps.setString(2, a.getNombre());
            ps.setString(3, a.getAp_paterno());
            ps.setString(4, a.getAp_materno());
            ps.setString(5, a.getDireccion());
            ps.setString(6, a.getTelefono());
            ps.setString(7, a.getGrado());
            ps.setInt(8, a.getJornada());
            ps.setInt(9, a.getId_policia_pie_tierra());
            ps.executeUpdate();
        } finally {
            cerrar(ps);
            cerrar(cnn);
        } 
    }
    public List loadSelect(PoliciaClass a, Connection cnn) 
        throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = cnn.prepareStatement(SQL_SELECT);
            int aux= a.getId_policia_pie_tierra();
            ps.setInt(1, aux);
            rs = ps.executeQuery();
            List resultados = getResults(rs);
            if (resultados.size() > 0) {
                return resultados;
            } else {
                return null;
            }
        } finally {
            cerrar(rs);
            cerrar(ps);
            cerrar(cnn);
        }
    }
    
    public List loadSelectSector(PoliciaClass a, Connection cnn) 
        throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = cnn.prepareStatement(SQL_SELECT_SECTOR);
            ps.setString(1, a.getNombre());
            ps.setString(2, a.getNombre());
            ps.setString(3, a.getNombre());
            ps.setInt(4, a.getSector());
            rs = ps.executeQuery();
            List resultados = getResults(rs);
            if (resultados.size() > 0) {
                return resultados;
            } else {
                return null;
            }
        } finally {
            cerrar(rs);
            cerrar(ps);
            cerrar(cnn);
        }
    }
    
        public void borrarPolicia(PoliciaClass a,Connection conn) 
        throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(SQL_DELETE);
            ps.setInt(1,a.getId_policia_pie_tierra());
            ps.executeUpdate();
        } finally {
            cerrar(rs);
            cerrar(ps);
            cerrar(conn);
          
        }
   }
        
        public List loadAll(Connection conn)
            throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(SQL_SELECT_ALL);
            rs = ps.executeQuery();
            List resultados = getResults(rs);
            if (resultados.size() > 0) {
                return resultados;
            } else {
                return null;
            }
        } finally {
            cerrar(rs);
            cerrar(ps);
            cerrar(conn);
        }
    }
    
    
    
    private void cerrar(Connection cnn) {
        if (cnn != null) {
            try {
                cnn.close();
            } catch (SQLException e) {
                //manejo del error
            }
        }
    }

    private void cerrar(PreparedStatement ps) {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                //manejo del error
            }
        }
    }

    private void cerrar(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                //manejo del error
            }
        }
    }
    
    private List getResults(ResultSet rs)
            throws SQLException {
        List results = new ArrayList();
        while (rs.next()) {
            PoliciaClass a = new PoliciaClass();
            a.setId_policia_pie_tierra(rs.getInt("id_recurso"));
            //a.setSector(rs.getInt("sector_id_sector"));
            a.setCrp(rs.getInt("CARRO_RADIO_PATRULLA_id_unidad"));
            a.setNombre(rs.getString("nombre"));
            a.setAp_paterno(rs.getString("ap_paterno"));
            a.setAp_materno(rs.getString("ap_materno"));
            a.setDireccion(rs.getString("direccion"));
            a.setTelefono(rs.getString("telefono"));
            a.setGrado(rs.getString("grado"));
            a.setJornada(rs.getInt("jornada"));
            results.add(a);
        }
        return results;
    }
    

  
    
}
